# -*- coding: utf-8 -*-            
# @Author : HJH
# @Time : 2024/5/15 11:59
# @Use :
import json
import time
import pandas as pd
from itertools import groupby


def group_util(data_list: list, field: str):
    # 首先对字典列表按照指定的键进行排序（通常分组之前要进行排序）
    sorted_dict_list = sorted(data_list, key=lambda x: x[field])

    # 使用itertools.groupby进行分组
    grouped_dict = {}
    for key, group in groupby(sorted_dict_list, key=lambda x: x[field]):
        grouped_dict[key] = list(group)

    return grouped_dict


# 读取第一张表的数据
# df1 = pd.read_excel(r'C:\Users\Mmdsj\Desktop\数据表\销售报表\QN\6月\QN销售明细报表20240617-23.xlsx')
df1 = pd.read_excel(r'C:\Users\Mmdsj\Desktop\同程\销售报表\8月\销售明细报表20240826-29.xlsx')
#
df1 = df1.dropna(subset=['利润'])

# 将合并后的DataFrame转换为字典列表
dict_list = df1.to_dict(orient='records')

grouped_dict = group_util(dict_list, '出票日期')

# 遍历每个分组
for key, group in grouped_dict.items():
    # xp_tickets = [item for item in group if 'YCHKCP' in item['政策来源'] and item['销售店铺'] == '去哪儿']
    # xp_ticket_num = len(xp_tickets)
    # xp_ticket_profit = int(sum(item['利润'] for item in xp_tickets))
    # print(f"{key} 产品票量:{xp_ticket_num} 产品利润:{xp_ticket_profit}")

    pz_tickets = [item for item in group if 'TC' in item['政策来源']]
    pz_ticket_num = len(pz_tickets)
    pz_ticket_profit = int(sum(item['利润'] for item in pz_tickets))
    pz_chd_num = round(len([item for item in pz_tickets if item['乘机类型'] == '儿童']) * 100 / len(pz_tickets), 1)
    print(f"{key} 普政票量:{pz_ticket_num} 普政利润:{pz_ticket_profit} 儿童比例:{pz_chd_num}")


hs_grouped_dict = group_util(dict_list, '航司')
# 遍历每个分组
res_hs_list = []
for key, group in hs_grouped_dict.items():
    hs_result = dict()
    pzhs_tickets = [item for item in group if 'TC' in item['政策来源']]
    if len(pzhs_tickets) == 0:
        continue

    hs_result['航司'] = key
    hs_result['票量'] = len(pzhs_tickets)
    hs_result['利润'] = int(sum(item['利润'] for item in pzhs_tickets))
    hs_result['单张'] = round(hs_result['利润'] / hs_result['票量'], 1)
    hs_result['儿童比例'] = round(len([item for item in pzhs_tickets if item['乘机类型'] == '儿童']) * 100 / len(pzhs_tickets), 1)
    hs_result['儿童数量'] = round(hs_result['票量'] * 0.01 * hs_result['儿童比例'], 1)
    res_hs_list.append(hs_result)
print(json.dumps(res_hs_list))

# df = pd.DataFrame(dict_list)
#
# # 保存合并后的数据到Excel文件
# df.to_excel('合并后的数据.xlsx', index=False)